﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.Util
{
   public class Data
    {
        private string  _conStr;

        public string  ConnectionString
        {
            get { return _conStr; }
            set { _conStr = value; }
        }

       public Data(string conStr)
       {
           _conStr = conStr;
       }
       public decimal AllOutput(DateTime dateF, DateTime dateT, Int32? goodsId, Int32? depId)
       {
           string QueryString = @"SELECT sum(CA.GoodsDocLines.RetailSum)
FROM CD.Parties INNER JOIN
CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
where CA.Documents.DocDate >= @dateF AND CA.Documents.DocDate < @dateT
AND CA.GoodsDocLines.RetailSum < 0";
           string GoodsFilter = @" AND CD.Parties.GoodsId = @GoodsId ";
           string DepartFilter = @" AND CA.Documents.DepartmentId = @DeptId ";
           decimal result = 0;
           #region using
           using (SqlConnection connection = new SqlConnection(ConnectionString))
           {
               try
               {
                   SqlCommand command = new SqlCommand();
                   SqlParameter FDate = new SqlParameter("@dateF", SqlDbType.DateTime);
                   FDate.Value = dateF;
                   command.Parameters.Add(FDate);
                   SqlParameter TDate = new SqlParameter("@dateT", SqlDbType.DateTime);
                   TDate.Value = dateT;
                   command.Parameters.Add(TDate);
                   if (goodsId.HasValue)
                   {
                       SqlParameter GoodsId = new SqlParameter(@"GoodsId", SqlDbType.Int);
                       GoodsId.Value = goodsId.Value;
                       QueryString += GoodsFilter;
                       command.Parameters.Add(GoodsId);
                   }
                   if (depId.HasValue)
                   {
                       SqlParameter DeptId = new SqlParameter("@DeptId", SqlDbType.Int);
                       DeptId.Value = depId.Value;
                       command.Parameters.Add(DeptId);
                       QueryString += DepartFilter;
                   }
                   command.CommandText = QueryString;
                   command.Connection = connection;
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                   {
                       return result;
                   }
                   result = Convert.ToDecimal(obj) * -1;
                   return result;
               }
               catch (SqlException se)
               {
                   throw se;
               }
               catch (Exception ee)
               {
                   throw ee;
               }
           }
           #endregion
       }
       /// <summary>
       /// возврат поставщику
       /// </summary>
       /// <param name="dateF"></param>
       /// <param name="dateT"></param>
       /// <param name="goodsId"></param>
       /// <param name="depId"></param>
       /// <returns></returns>
       public decimal ReturnSupplier(DateTime dateF, DateTime dateT, Int32? goodsId, Int32? depId)
       {
           string QueryString =
          @"SELECT sum(CA.GoodsDocLines.RetailSum)
FROM CD.Parties INNER JOIN
CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
where CA.Documents.DocDate BETWEEN @dateF AND @dateT
AND CA.Documents.DocTypeId = 8  ";
           string GoodsFilter = @" AND CD.Parties.GoodsId = @GoodsId ";
           string DepartFilter = @" AND CA.Documents.DepartmentId = @DeptId ";
           decimal result = 0;
           #region using
           using (SqlConnection connection = new SqlConnection(ConnectionString))
           {
               try
               {
                   SqlCommand command = new SqlCommand();
                   SqlParameter FDate = new SqlParameter("@dateF", SqlDbType.DateTime);
                   FDate.Value = dateF;
                   command.Parameters.Add(FDate);
                   SqlParameter TDate = new SqlParameter("@dateT", SqlDbType.DateTime);
                   TDate.Value = dateT;
                   command.Parameters.Add(TDate);
                   if (goodsId.HasValue)
                   {
                       SqlParameter GoodsId = new SqlParameter(@"GoodsId", SqlDbType.Int);
                       GoodsId.Value = goodsId.Value;
                       QueryString += GoodsFilter;
                       command.Parameters.Add(GoodsId);
                   }
                   if (depId.HasValue)
                   {
                       SqlParameter DeptId = new SqlParameter("@DeptId", SqlDbType.Int);
                       DeptId.Value = depId.Value;
                       command.Parameters.Add(DeptId);
                       QueryString += DepartFilter;
                   }
                   command.CommandText = QueryString;
                   command.Connection = connection;
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                   {
                       return result;
                   }
                   result = Convert.ToDecimal(obj) * -1;
                   return result;
               }
               catch (SqlException se)
               {
                   throw se;
               }
               catch (Exception ee)
               {
                   throw ee;
               }
           }
           #endregion

       }
       /// <summary>
       /// сумма на дату
       /// </summary>
       /// <param name="date"></param>
       /// <param name="goodsId"></param>
       /// <param name="depId"></param>
       /// <returns></returns>
       public decimal SumFromDateB(DateTime date,Int32? goodsId, Int32? depId)
       {
           string QueryString =
@"SELECT --sum(CA.GoodsDocLines.Qntty * CD.Parties.RetailPrice)
sum(CA.GoodsDocLines.RetailSum)
FROM CD.Parties INNER JOIN
CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
where CA.Documents.DocDate < @DocDate ";
           string GoodsFilter = @" AND CD.Parties.GoodsId = @GoodsId ";
           string DepartFilter = @" AND CA.Documents.DepartmentId = @DeptId ";
           decimal result = 0;
           using (SqlConnection connection = new SqlConnection(ConnectionString))
           {
               try
               {
                   SqlCommand command = new SqlCommand();
                   SqlParameter DocDate = new SqlParameter("@DocDate", SqlDbType.DateTime);
                   DocDate.Value = date;
                   command.Parameters.Add(DocDate);
                   if (goodsId.HasValue)
                   {
                       SqlParameter GoodsId = new SqlParameter(@"GoodsId", SqlDbType.Int);
                       GoodsId.Value = goodsId.Value;
                       QueryString += GoodsFilter;
                       command.Parameters.Add(GoodsId);
                   }
                   if (depId.HasValue)
                   {
                       SqlParameter DeptId = new SqlParameter("@DeptId", SqlDbType.Int);
                       DeptId.Value = depId.Value;
                       command.Parameters.Add(DeptId);
                       QueryString += DepartFilter;
                   }
                   command.CommandText = QueryString;
                   command.Connection = connection;
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                   {
                       return result;
                   }
                   result = Convert.ToDecimal(obj);
                   return result;
               }
               catch (SqlException se)
               {
                   throw se;
               }
               catch (Exception ee)
               {
                   throw ee;
               }
           }
       }
       public decimal SumFromDateE(DateTime date, Int32? goodsId, Int32? depId)
       {
           string QueryString =
@"SELECT --sum(CA.GoodsDocLines.Qntty * CD.Parties.RetailPrice)
sum(CA.GoodsDocLines.RetailSum)
FROM CD.Parties INNER JOIN
CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
where CA.Documents.DocDate <= @DocDate ";
           string GoodsFilter = @" AND CD.Parties.GoodsId = @GoodsId ";
           string DepartFilter = @" AND CA.Documents.DepartmentId = @DeptId ";
           decimal result = 0;
           using (SqlConnection connection = new SqlConnection(ConnectionString))
           {
               try
               {
                   SqlCommand command = new SqlCommand();
                   SqlParameter DocDate = new SqlParameter("@DocDate", SqlDbType.DateTime);
                   DocDate.Value = date;
                   command.Parameters.Add(DocDate);
                   if (goodsId.HasValue)
                   {
                       SqlParameter GoodsId = new SqlParameter(@"GoodsId", SqlDbType.Int);
                       GoodsId.Value = goodsId.Value;
                       QueryString += GoodsFilter;
                       command.Parameters.Add(GoodsId);
                   }
                   if (depId.HasValue)
                   {
                       SqlParameter DeptId = new SqlParameter("@DeptId", SqlDbType.Int);
                       DeptId.Value = depId.Value;
                       command.Parameters.Add(DeptId);
                       QueryString += DepartFilter;
                   }
                   command.CommandText = QueryString;
                   command.Connection = connection;
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                   {
                       return result;
                   }
                   result = Convert.ToDecimal(obj);
                   return result;
               }
               catch (SqlException se)
               {
                   throw se;
               }
               catch (Exception ee)
               {
                   throw ee;
               }
           }
       }
       public decimal DiscountSum(DateTime dateF, DateTime dateT)
       {
           string QueryString = @"SELECT   sum ( -1 * (CA.GoodsDocLines.Qntty * CD.Parties.RetailPrice - CA.GoodsDocLines.RetailSum))
FROM  CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id
WHERE     (CA.Documents.DocTypeId = 3) AND 
(CA.Documents.DocDate >= @dateF) AND 
(CA.Documents.DocDate < @dateT) and DiscountValue > 0";
           using (SqlConnection connection = new SqlConnection(ConnectionString))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.Parameters.AddWithValue("@dateF", dateF);
                   command.Parameters.AddWithValue("@dateT", dateT);
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                       return 0;
                   decimal res = Convert.ToDecimal(obj);
                   return res;
               }
               catch (SqlException se)
               {
                   string s = se.ToString();
                   throw se;
               }
               catch (Exception ee)
               {
                   string s = ee.ToString();
                   throw ee;
               }
           }
       }
    }
}
